﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_MAS_ExpenseRates : IDAL_FIM_MAS_ExpenseRates
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public IList<M_FIM_MAS_ExpenseRates> GetFull(Nullable<int> bgm_exp_seq, Nullable<int> bgm_source_seq)
        {
            string strSQL = "";
            DataRowCollection drc = null;
            IList<M_FIM_MAS_ExpenseRates> infoList = new List<M_FIM_MAS_ExpenseRates>();
            try
            {
                //int n = exp_rate_type_seq.Length;
                //string strSelect = "";
                string strJoin = "";
                string strFilter = "";

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);

                //for (int i = 0; i < n; i++)
                //{
                //    dbManager.AddParameters(3 + i, "@exp_rate_type_seq" + i.ToString(), exp_rate_type_seq[i]);
                //    strFilter += (exp_rate_type_seq[i] != null ? 
                //        string.Format(" and map{0}.exp_rate_type_seq = @exp_rate_type_seq{0}", i.ToString()) : "");
                //    //strSelect += string.Format(", map{0}.exp_rate_type_seq", i.ToString());
                //    strJoin += string.Format(" LEFT JOIN fim_mas_expense_rate_mappings map{0} ON r.exp_rate_seq = map{0}.exp_rate_seq", i.ToString());
                //}

                strSQL = "SELECT DISTINCT r.exp_rate_seq, r.bgm_exp_seq, r.exp_rate_name, r.exp_rate_descr"
                    + ", r.amount_min, r.amount_max, r.bgm_source_seq, r.relate_man, r.relate_day"

                    + ", r.record_status, r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_mas_expense_rates r"
                    //+ strJoin
                    + " WHERE r.record_status not like @delete_status"
                    + (bgm_exp_seq != null ? " and r.bgm_exp_seq = @bgm_exp_seq" : "")
                    + (bgm_source_seq != null ? " and r.bgm_source_seq = @bgm_source_seq" : "")
                    + strFilter;

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_ExpenseRates info = new M_FIM_MAS_ExpenseRates();
                        info.exp_rate_seq = Convert.ToInt32(drc[i]["exp_rate_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]);
                        info.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        info.exp_rate_name = drc[i]["exp_rate_name"].ToString();
                        info.exp_rate_descr = drc[i]["exp_rate_descr"].ToString();
                        info.amount_min = cn.ConvertNullDouble(drc[i]["amount_min"]);
                        info.amount_max = cn.ConvertNullDouble(drc[i]["amount_max"]);
                        info.relate_man = drc[i]["relate_man"].ToString();
                        info.relate_day = drc[i]["relate_day"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MAS_ExpenseRates> GetList(Nullable<int> bgm_exp_seq, Nullable<int> bgm_source_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_ExpenseRates> infoList = new List<M_FIM_MAS_ExpenseRates>();
            try
            {
                //int n = exp_rate_type_seq.Length;
                //string strSelect = "";
                string strJoin = "";
                string strFilter = "";

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);

                //for (int i = 0; i < n; i++)
                //{
                //    dbManager.AddParameters(3 + i, "@exp_rate_type_seq" + i.ToString(), exp_rate_type_seq[i]);
                //    strFilter += (exp_rate_type_seq[i] != null ?
                //        string.Format(" and map{0}.exp_rate_type_seq = @exp_rate_type_seq{0}", i.ToString()) : "");
                //    //strSelect += string.Format(", map{0}.exp_rate_type_seq", i.ToString());
                //    strJoin += string.Format(" LEFT JOIN fim_mas_expense_rate_mappings map{0} ON r.exp_rate_seq = map{0}.exp_rate_seq", i.ToString());
                //}

                strSQL = "SELECT distinct r.exp_rate_seq, r.exp_rate_name"
                    + " FROM fim_mas_expense_rates r"
                    //+ strJoin
                    + " WHERE r.record_status not like @delete_status"
                    + (bgm_exp_seq != null ? " and r.bgm_exp_seq = @bgm_exp_seq" : "")
                    + (bgm_source_seq != null ? " and r.bgm_source_seq = @bgm_source_seq" : "")
                    + strFilter;

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_ExpenseRates info = new M_FIM_MAS_ExpenseRates();
                        info.exp_rate_seq = Convert.ToInt32(drc[i]["exp_rate_seq"]);
                        info.exp_rate_name = drc[i]["exp_rate_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MAS_ExpenseRates> GetListByClassSeq(Nullable<int> bgm_exp_seq, Nullable<int> bgm_source_seq
            , Nullable<int> class_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_ExpenseRates> infoList = new List<M_FIM_MAS_ExpenseRates>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@class_seq", class_seq);

                strSQL = "select distinct r.exp_rate_seq, r.exp_rate_name"
                    //+ ", r.amount_min, r.amount_max"
                    //+ ", rm.exp_rate_type_seq"
                    //+ ", rt.relate_day, rt.relate_man"
                    //+ ", rt.relate_hour, rt.relate_minute"

                    + " from fim_mas_expense_rates r"
                    + " left join fim_mas_expense_rate_mappings rm on r.exp_rate_seq = rm.exp_rate_seq"
                    + " inner join fim_mas_expense_rate_types rt on rm.exp_rate_type_seq = rt.exp_rate_type_seq"
	                    + " and ("
	                    + "(@class_seq >= rt.class_seq_min and @class_seq <= rt.class_seq_max)"
	                    + " or (@class_seq >= rt.pos_type_1_min and @class_seq <= rt.pos_type_1_max)"
	                    + " or (@class_seq >= rt.pos_type_2_min and @class_seq <= rt.pos_type_2_max)"
	                    + " or (@class_seq >= rt.pos_type_3_min and @class_seq <= rt.pos_type_3_max)"
	                    + " or (@class_seq >= rt.pos_type_4_min and @class_seq <= rt.pos_type_4_max)"
	                    + ")"
                    + " where record_status not like @delete_status"
                    + (bgm_exp_seq != null ? " and r.bgm_exp_seq = @bgm_exp_seq" : "")
                    + (bgm_source_seq != null ? " and r.bgm_source_seq = @bgm_source_seq" : "")
                    ;

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_ExpenseRates info = new M_FIM_MAS_ExpenseRates();
                        info.exp_rate_seq = Convert.ToInt32(drc[i]["exp_rate_seq"]);
                        info.exp_rate_name = drc[i]["exp_rate_name"].ToString();
                        //info.amount_min = cn.ConvertNullInt(drc[i]["amount_min"]);
                        //info.amount_max = cn.ConvertNullInt(drc[i]["amount_max"]);
                        //info.relate_day = drc[i]["relate_day"].ToString();
                        //info.relate_man = drc[i]["relate_man"].ToString();
                        //info.relate_hour = drc[i]["relate_hour"].ToString();
                        //info.relate_minute = drc[i]["relate_minute"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MAS_ExpenseRates GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MAS_ExpenseRates info = new M_FIM_MAS_ExpenseRates();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT r.exp_rate_seq, r.bgm_source_seq, r.bgm_exp_seq, r.exp_rate_name"
                    + ", r.exp_rate_descr, r.amount_min, r.amount_max, r.relate_man, r.relate_day"

                    + ", r.record_status, r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_mas_expense_rates r"
                    + " WHERE r.exp_rate_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.exp_rate_seq = Convert.ToInt32(drc[0]["exp_rate_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.bgm_exp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_seq"]);
                    info.exp_rate_name = drc[0]["exp_rate_name"].ToString();
                    info.exp_rate_descr = drc[0]["exp_rate_descr"].ToString();
                    info.amount_min = cn.ConvertNullDouble(drc[0]["amount_min"]);
                    info.amount_max = cn.ConvertNullDouble(drc[0]["amount_max"]);
                    info.relate_man = drc[0]["relate_man"].ToString();
                    info.relate_day = drc[0]["relate_day"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq from fim_mas_expense_rates";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_MAS_ExpenseRates info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@exp_rate_seq", info.exp_rate_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_seq", info.bgm_exp_seq);
                dbManager.AddParameters(3, "@exp_rate_name", info.exp_rate_name);
                dbManager.AddParameters(4, "@exp_rate_descr", info.exp_rate_descr);
                dbManager.AddParameters(5, "@amount_min", info.amount_min);
                dbManager.AddParameters(6, "@amount_max", info.amount_max);

                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", info.created_by);
                dbManager.AddParameters(9, "@created_when", info.created_when);

                dbManager.AddParameters(10, "@relate_man", info.relate_man);
                dbManager.AddParameters(11, "@relate_day", info.relate_day);

                strSQL = "INSERT INTO fim_mas_expense_rates"
                    + "(bgm_source_seq, bgm_exp_seq, exp_rate_name, exp_rate_descr"
                    + ", amount_min, amount_max, relate_man, relate_day"
                    + ", record_status, created_by, created_when)"
                    + " VALUES (@bgm_source_seq"
                        + ", @bgm_exp_seq"
                        + ", @exp_rate_name"
                        + ", @exp_rate_descr"
                        + ", @amount_min"
                        + ", @amount_max"
                        + ", @relate_man"
                        + ", @relate_day"
                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ");"
                    + " select scope_identity()";
                var strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                return strResult;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MAS_ExpenseRates info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@exp_rate_seq", info.exp_rate_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_seq", info.bgm_exp_seq);
                dbManager.AddParameters(3, "@exp_rate_name", info.exp_rate_name);
                dbManager.AddParameters(4, "@exp_rate_descr", info.exp_rate_descr);
                dbManager.AddParameters(5, "@amount_min", info.amount_min);
                dbManager.AddParameters(6, "@amount_max", info.amount_max);

                dbManager.AddParameters(7, "@updated_by", info.updated_by);
                dbManager.AddParameters(8, "@updated_when", info.updated_when);

                dbManager.AddParameters(9, "@relate_man", info.relate_man);
                dbManager.AddParameters(10, "@relate_day", info.relate_day);

                strSQL = "UPDATE fim_mas_expense_rates"
                    + " SET bgm_source_seq = @bgm_source_seq"
                    + ", bgm_exp_seq = @bgm_exp_seq"
                    + ", exp_rate_name = @exp_rate_name"
                    + ", exp_rate_descr = @exp_rate_descr"
                    + ", amount_min = @amount_min"
                    + ", amount_max = @amount_max"
                    + ", relate_man = @relate_man"
                    + ", relate_day = @relate_day"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE exp_rate_seq = @exp_rate_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MAS_ExpenseRates info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@exp_rate_seq", info.exp_rate_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_mas_expense_rates"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE exp_rate_seq = @exp_rate_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
